﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Windows;
using System.Windows.Controls;
using System.Windows.Data;
using System.Windows.Documents;
using System.Windows.Input;
using System.Windows.Media;
using System.Windows.Media.Imaging;
using System.Windows.Shapes;

using Excel = Microsoft.Office.Interop.Excel;
using ExcelLibrary;
using ExcelLibrary.SpreadSheet;



using cecbData;
using cecbClasses.Data_Adapters;
using System.Data;
using System.Collections.ObjectModel;
using cecbClasses;
using System.IO;
using System.Diagnostics;
using cecbClasses.Data_Adapters.Project;
namespace CECB
{
    /// <summary>
    /// Interaction logic for ActivationCodeEmail_Window.xaml
    /// </summary>
    public partial class ActivationCodeEmail_Window : Window
    {
        // Dss.Methods.ActivationCodeEmail em;
        cecbClasses.Data_Adapters.Email.ActivationCodeEmail em;


        public ActivationCodeEmail_Window()
        {
            InitializeComponent();
            em = new cecbClasses.Data_Adapters.Email.ActivationCodeEmail();

            TeamAutocompleteTextBox.ItemsSource = em.getProjectDetails();
            buttonSendAllEmail.IsEnabled = false;
            button3.IsEnabled = false;

        }

        private void TextBox_selectMemeber_SelectionChanged(object sender, SelectionChangedEventArgs e)
        {

        }

        private void Window_Loaded(object sender, RoutedEventArgs e)
        {
           
        }

        private void comboBox_project_DropDownOpened(object sender, EventArgs e)
        {
            //  comboBox_project.ItemsSource = em.getAllProjectNames();
        }

        private void comboBox_project_SelectionChanged(object sender, SelectionChangedEventArgs e)
        {
            //    Label_projectTeam.Content = em.getTeamRefByProRef(comboBox_project.SelectedValue.ToString());
        }

        private void ReferenceTextBox_SelectionChanged(object sender, SelectionChangedEventArgs e)
        {


        }

         

        Projects prj = null;

        private void TeamReferenceTextBox_SelectionChanged(object sender, SelectionChangedEventArgs e)
        {

            try
            {
                if (listBoxComponents.HasItems)
                {
                    listBoxComponents.ItemsSource = null;
                    listBoxComponents.Items.Clear();
                }
                if (listBoxMembers.HasItems)
                {
                    listBoxMembers.ItemsSource = null;
                    listBoxMembers.Items.Clear();
                }

                cecbClasses.Data_Adapters.Slider slid = new cecbClasses.Data_Adapters.Slider();
                prj = (Projects)TeamAutocompleteTextBox.SelectedItem;
                String pref = prj.proj_reference.ToString();
                Label_projectTeam.Content = new cecbData.DSSEntities().Projects.First(i => i.proj_reference.Equals(pref)).proj_team; // em.getTeamRefByProRef(TeamAutocompleteTextBox.Text);
                 
                listBoxMembers.ItemsSource = new cecbClasses.Data_Adapters.Member.Func_Members().getAllMemberNames(Label_projectTeam.Content.ToString());

                cecbData.DSSEntities dataContext = new cecbData.DSSEntities();

                IQueryable<Components> qComponents = (from c in dataContext.ProjectImpacts
                                                      join b in dataContext.Impacts on c.impt_reference equals b.impt_reference
                                                      join d in dataContext.Components on b.impt_component equals d.cmpt_reference
                                                      where c.proj_reference == pref
                                                      orderby b.impt_component
                                                      select d).Distinct();

                listBoxComponents.ItemsSource = qComponents;
            }
            catch (Exception)
            {

            }


        }
        private void listBoxMembers_SelectionChanged(object sender, SelectionChangedEventArgs e)
        {
            cecbData.Members mem = (cecbData.Members)listBoxMembers.SelectedItem;

            teamMember.Content = mem.mem_name;

        }

        private void Image_MouseLeftButtonDown(object sender, MouseButtonEventArgs e)
        {
            this.Close();
        }

        private void button2_Click_1(object sender, RoutedEventArgs e)
        {

        }

        private void buttonExcelGenarate_Click(object sender, RoutedEventArgs e)
        {
            if (RdbLeo.IsChecked == false && RdbSim.IsChecked == false)
            {
                MessageBox.Show("Select a matrix type", "Message", MessageBoxButton.OK, MessageBoxImage.Information);
            }
            else
            {
                if (listBoxMembers.SelectedIndex > -1)
                {

                    if (listBoxComponents.SelectedIndex > -1)
                    {


                        try
                        {
                            int x = listBoxComponents.SelectedIndex;
                            var itemContainer = listBoxComponents.ItemContainerGenerator.ContainerFromIndex(x) as ListBoxItem;
                            itemContainer.Background = Brushes.Green;
                            Components cmp = (Components)listBoxComponents.SelectedItem;
                            Members mem = (Members)listBoxMembers.SelectedItem;
                            String projID = ((Projects)TeamAutocompleteTextBox.SelectedItem).proj_reference;
                            String projNAme = ((Projects)TeamAutocompleteTextBox.SelectedItem).proj_name;
                            if (RdbLeo.IsChecked == true)
                            {

                                // MessageBox.Show(cmp.cmpt_name.ToString());

                                genarateExcellLeopold(cmp.cmpt_name, mem, projID, projNAme);
                                buttonSendAllEmail.IsEnabled = true;
                                button3.IsEnabled = true;
                            }
                            else if (RdbSim.IsChecked == true)
                            {

                                genarateExcellSimple(cmp.cmpt_name, mem, projID, projNAme);
                                buttonSendAllEmail.IsEnabled = true;
                                button3.IsEnabled = true;

                            }
                        }
                        catch (Exception ex)
                        {

                            MessageBox.Show("" + ex.Message, "Message", MessageBoxButton.OK, MessageBoxImage.Information);
                        }
                    }
                    else
                    {

                        MessageBox.Show("Select a component", "Message", MessageBoxButton.OK, MessageBoxImage.Information);
                    }



                }
                else
                    MessageBox.Show("Select a member", "Message", MessageBoxButton.OK, MessageBoxImage.Information);

            }

        }


        #region =============================== EmailGenaration =====================================================

        string destFile;
        string destFile1;

        Func_Projects ObjProjctsFunc = new Func_Projects();

        Excel.Application oXL;
        Excel._Workbook oWB;
        Excel._Worksheet oSheet;
        Excel.Range oRng;
        String filePath = "";

        public void genarateExcellLeopold(String com, Members mem, String projid, String projName)
        {

            try
            {

                string startColName, endColName;
                int startColNo, endColNo;


                oXL = new Excel.Application();
                oXL.Visible = false;

                oWB = (Excel._Workbook)(oXL.Workbooks.Add());
                oSheet = (Excel._Worksheet)oWB.ActiveSheet;




                cecbClasses.Data_Adapters.Excel.CreateExcell mm = new cecbClasses.Data_Adapters.Excel.CreateExcell();
                DataAdapter da = new DataAdapter();
                Projects prj = (Projects)TeamAutocompleteTextBox.SelectedItem;
                List<Get_EmptyActivity_Phase_Result> LstResult = mm.GetEmptyActivitiesValues(prj.proj_reference);

              
                List<Activities> lstActivities = mm.getActivity(prj.proj_reference);

                 
                List<Impacts> lstImpacts = mm.getImpactsByComponent(prj.proj_reference ,com);

                
                List<Components> lstcmpt = mm.getComponent(prj.proj_reference, com);

                List<Get_Leopold_Matric_Result> lstMatrice = ObjProjctsFunc.GetLeopolMatrices(prj.proj_reference);

                 
                oSheet.Cells[3, 1] = "Category";
                oSheet.get_Range("A3").ColumnWidth = 15;
                oSheet.get_Range("A3").Borders.Weight = 2;
                

                oSheet.Cells[3, 2] = "Component";
                oSheet.get_Range("B3").ColumnWidth = 15;

                oSheet.Cells[3, 4] = "Project Impact";
                oSheet.get_Range("D3").ColumnWidth = 40;
                oSheet.get_Range("C3", "D3").Merge();

                oSheet.get_Range("A3", "D3").Font.Bold = true;
                oSheet.get_Range("A3", "D3").VerticalAlignment = VerticalAlignment.Center;


                oSheet.Cells[1, 2] = projid;
                oSheet.get_Range("A1").EntireRow.Hidden = true;
                oSheet.Cells[1, 3] = projName;
               
                oSheet.Cells[1, 1] = mem.mem_reference;
              
                oSheet.get_Range("C1", "D1").Merge();
                int z = 5;
                startColNo = 5;
                startColName = GetExcelColumnName(startColNo) + "2";


                oXL.Visible = true;
                oXL.UserControl = true;
                 
                foreach (Get_EmptyActivity_Phase_Result oRsts in LstResult)
                {
                    endColNo = startColNo + (((int)oRsts.NoOfActivities) * 2) - 1;
                    endColName = GetExcelColumnName(endColNo) + "2";

                    oSheet.Cells[2, z] = oRsts.actv_phase;

                    oSheet.get_Range(startColName, endColName).Font.Bold = true;

                    oSheet.get_Range(startColName, endColName).Merge();
                    oSheet.get_Range(startColName, endColName).HorizontalAlignment = HorizontalAlignment.Center;
                    oSheet.get_Range(startColName, endColName).Borders.Weight = 2;
                    startColNo = endColNo + 1;
                    startColName = GetExcelColumnName(startColNo) + "2";

                    z = z + (int)oRsts.NoOfActivities * 2;
                    
                }
                 
                startColNo = 1;
                startColName = GetExcelColumnName(startColNo) + "4";

                int startColNo2 = 1;
                string startColName2 = GetExcelColumnName(startColNo2) + "3";
                int endColNo2;
                string endColName2;
                 
                int ActNo = 1;
                z = 5;
                foreach (Activities oRstAct in lstActivities)
                {
                    endColNo2 = startColNo2 + 1;
                    endColName2 = GetExcelColumnName(endColNo2) + "3";

                    endColNo = startColNo + 1;
                    endColName = GetExcelColumnName(endColNo) + "4";

                    oSheet.Cells[3, z] = ActNo;
                    oSheet.get_Range(GetExcelColumnName(z) + 3).Borders.Weight = 2;
                    oSheet.Cells[4, z] = oRstAct.actv_name;

                    oSheet.Cells[4, z].Font.Bold = true;
                    oSheet.Cells[4, z].Orientation = 90;
                    oSheet.Cells[4, z].RowHeight = 200;
                    oSheet.Cells[4, z].WrapText = true;
                    oSheet.Cells[4, z].Merge();
                    oSheet.Cells[4, z].Borders.Weight = 2;

                    oSheet.get_Range(GetExcelColumnName(z) + "4", GetExcelColumnName(z + 1) + "4").Merge();
                    oSheet.get_Range(GetExcelColumnName(z) + "3", GetExcelColumnName(z + 1) + "3").Merge();
                    oSheet.get_Range(GetExcelColumnName(z) + "4", GetExcelColumnName(z + 1) + "4").Font.Bold = true;
                    oSheet.get_Range(GetExcelColumnName(z) + "4", GetExcelColumnName(z + 1) + "4").HorizontalAlignment = HorizontalAlignment.Center;
                    oSheet.get_Range(GetExcelColumnName(z) + "4", GetExcelColumnName(z + 1) + "4").Borders.Weight = 2;
                     
                    startColNo = startColNo + 2;
                    startColName = GetExcelColumnName(startColNo) + "4";

                    startColNo2 = startColNo2 + 2;
                    startColName2 = GetExcelColumnName(startColNo2) + "3";
                    z = z + 2;

                    ActNo = ActNo + 1;
                }
                 
                startColNo = 1;
                startColName = GetExcelColumnName(startColNo) + "5";

                string srtImpColNoName, endImpColNoName, srtImpColName, endImpColName;

                int srtCatNo, srtSubNo;
                int LineNO = 1;
                int rowNo = 5;


                string CatName = lstcmpt[0].cmpt_mainCat.ToString();
                oSheet.Cells[5, 1] = CatName;

                srtCatNo = 5;
                srtSubNo = 5;
                string SubName = lstcmpt[0].cmpt_name.ToString();
                oSheet.Cells[5, 2] = SubName;
                int x = 0;
                foreach (Components oRstImpacts in lstcmpt)
                {
                    z = 5;

                    if (CatName != oRstImpacts.cmpt_mainCat)
                    {
                        srtCatNo = rowNo;
                        CatName = oRstImpacts.cmpt_mainCat;
                        oSheet.Cells[rowNo, 1] = CatName;
                    }

                    if (SubName != oRstImpacts.cmpt_name)
                    {
                        srtSubNo = rowNo;
                        SubName = oRstImpacts.cmpt_name;
                        oSheet.Cells[rowNo, 2] = SubName;
                    }

                    startColNo = 1;
                    startColName = GetExcelColumnName(startColNo) + rowNo;

                    endColNo = startColNo + 1;
                    endColName = GetExcelColumnName(endColNo) + rowNo;

                    oSheet.Cells[rowNo, 4] = lstImpacts[x].impt_name;
                    x++;
                    oSheet.Cells[rowNo, 3] = LineNO;
                    srtImpColNoName = "C" + rowNo;
                    endImpColNoName = "C" + (rowNo + 1);

                    srtImpColName = "D" + rowNo;
                    endImpColName = "D" + (rowNo + 1);

                    oSheet.get_Range(srtImpColNoName, endImpColNoName).Merge();
                    oSheet.get_Range(srtImpColName, endImpColName).Merge();


                    oSheet.get_Range("A" + srtCatNo, "A" + (rowNo + 1)).Merge();

                    oSheet.get_Range("B" + srtSubNo, "B" + (rowNo + 1)).Merge();

                     
                    startColNo = 3;
                    foreach (Activities oRstAct in lstActivities)
                    {

                        startColNo = startColNo + 2;
                        startColName = GetExcelColumnName(startColNo) + rowNo;
                        endColNo = startColNo + 1;
                        endColName = GetExcelColumnName(endColNo) + rowNo;

                        oSheet.get_Range(startColName, endColName).ColumnWidth = 2.5;

                        DrawCellBorders(rowNo, z + 1, rowNo, z + 1, true, false, false, true, true);
                        DrawCellBorders(rowNo + 1, z, rowNo + 1, z, false, true, true, false, true);
                        DrawCellBorders(rowNo + 1, z + 1, rowNo + 1, z + 1, false, true, false, true, false);
                        z = z + 2;
                    }
                    endColName = GetExcelColumnName(startColNo) + (rowNo + 1).ToString();
                    rowNo = rowNo + 2;
                    LineNO = LineNO + 1;
                }
                 
                String date = DateTime.Now.ToShortDateString();
                String date1 = DateTime.Now.ToLongTimeString();
                String date2 = DateTime.Now.ToLongTimeString();

                String directPath = new cecbClasses.Data_Adapters.Setting.Func_Settings().getSettingValues(5);

                filePath = @""+directPath+@"\"+ prj.proj_name + "-" + prj.proj_reference + " - " + String.Format("{0: dd MMM yyyy}", DateTime.Now); ;
                filePath = @"D:\cecbTemp\" + prj.proj_name + "-" + prj.proj_reference + " - " + String.Format("{0: dd MMM yyyy}", DateTime.Now); ;

                bool folderExists = Directory.Exists(filePath);
                if (!folderExists)
                {
                    Directory.CreateDirectory(filePath);

                }

                oWB.SaveCopyAs(filePath + @"\" + mem.mem_name + "-" + com + "-Leopold.xlsx");
                MessageBox.Show("Excel File saved succsessfully", "Message", MessageBoxButton.OK, MessageBoxImage.Information);

            }
            catch (Exception)
            {
                throw;

            }

        }

        //====================================== Simle Matrix Ecxell Genarate ==========================
        public void genarateExcellSimple(String com, Members mem, String projid, String projName)
        {

            String pref = projid;

            string startColName, endColName;
            int startColNo, endColNo;
            oXL = new Excel.Application();
            oXL.Visible = true;

            //Get a new workbook.
            oWB = (Excel._Workbook)(oXL.Workbooks.Add());
            oSheet = (Excel._Worksheet)oWB.ActiveSheet;

            DataAdapter da = new DataAdapter();
            Projects prj = (Projects)TeamAutocompleteTextBox.SelectedItem;

            cecbClasses.Data_Adapters.Excel.CreateExcell mm = new cecbClasses.Data_Adapters.Excel.CreateExcell();
            List<Get_EmptyActivity_Phase_Result> LstResult = mm.GetEmptyActivitiesValues(prj.proj_reference);

            
            List<Activities> lstActivities = mm.getActivity(prj.proj_reference);

             
            List<Impacts> lstImpacts = mm.getImpacts(prj.proj_reference);

             
            List<Components> lstcmpt = mm.getComponent(prj.proj_reference, com);

           
            // ---- Header Row ---
            oSheet.Cells[3, 1] = "Category";
            oSheet.get_Range("A3").ColumnWidth = 15;
            oSheet.get_Range("A3").Borders.Weight = 2;
             

            oSheet.Cells[3, 2] = "Component";
            oSheet.get_Range("B3").ColumnWidth = 25;

            oSheet.Cells[3, 4] = "Project Impact";
            oSheet.get_Range("D3").ColumnWidth = 70;
            oSheet.get_Range("C3", "D3").Merge();

            oSheet.get_Range("A3", "D3").Font.Bold = true;
            oSheet.get_Range("A3", "D3").VerticalAlignment = VerticalAlignment.Center;

            int z = 5;
            startColNo = 5;
            startColName = GetExcelColumnName(startColNo).ToString() + "2";
            oSheet.Cells[1, 2] = projid;
            oSheet.Cells[1, 3] = projName;
            oSheet.Cells[1, 1] = mem.mem_reference;
            oSheet.Cells[2, 2] = "Simple Matrix";
            // --- Phases Headings -----
            foreach (Get_EmptyActivity_Phase_Result oRsts in LstResult)
            {
                endColNo = startColNo + (((int)oRsts.NoOfActivities  )  ) - 1;

                endColName = GetExcelColumnName(endColNo).ToString() + "2";

                 oSheet.Cells[2, z] = oRsts.actv_phase;

                oSheet.get_Range(startColName, endColName).Font.Bold = true;

                oSheet.get_Range(startColName, endColName).Merge();
                oSheet.get_Range(startColName, endColName).HorizontalAlignment = HorizontalAlignment.Center;
                oSheet.get_Range(startColName, endColName).Borders.Weight = 2;
                startColNo = endColNo + 1;
                startColName = GetExcelColumnName(startColNo).ToString() + "2";

                z = z + (int)oRsts.NoOfActivities;
              //  DrawCellBorders(2, z, 2, z, true, true, true, true, false);

            }

            //// --- Get Activities ---
            startColNo = 69;
            startColName = GetExcelColumnName(startColNo).ToString() + "4";

            int startColNo2 = 69;
            string startColName2 = GetExcelColumnName(startColNo2).ToString() + "3";
            int endColNo2;
            string endColName2;

            int ActNo = 1;
            z = 5;
            foreach (Activities oRstAct in lstActivities)
            {
                endColNo2 = startColNo2 + 1;
                endColName2 = GetExcelColumnName(endColNo2).ToString() + "3";

                endColNo = startColNo + 1;
                endColName = GetExcelColumnName(endColNo).ToString() + "4";

                oSheet.Cells[3, z] = ActNo;
                oSheet.get_Range(GetExcelColumnName(z) + 3).Font.Bold = true;
                oSheet.get_Range(GetExcelColumnName(z) + 3).Borders.Weight = 2;
                oSheet.Cells[4, z] = oRstAct.actv_name;

                oSheet.get_Range(GetExcelColumnName(z) + 4).Font.Bold = true;
                oSheet.get_Range(GetExcelColumnName(z)+ 4).Orientation = 90;
                oSheet.get_Range(GetExcelColumnName(z) + 4).RowHeight = 200;
                oSheet.get_Range(GetExcelColumnName(z) + 4).WrapText = true;
                oSheet.get_Range(GetExcelColumnName(z) + 4).Borders.Weight = 2;
                 
                oSheet.get_Range(startColName2, endColName2).Font.Bold = true;
                oSheet.get_Range(startColName2, endColName2).HorizontalAlignment = HorizontalAlignment.Center;
                 

                startColNo = startColNo + 1;
                startColName = GetExcelColumnName(startColNo).ToString() + "4";

                startColNo2 = startColNo2 + 1;
                startColName2 = GetExcelColumnName(startColNo2).ToString() + "3";
                z = z + 1;

                ActNo = ActNo + 1;
            }

            // --- Print Impacts , Magnitude, Imports
            // --- IMPACTS ---
            startColNo = 69;
            startColName = GetExcelColumnName(startColNo).ToString() + "5";

            string srtImpColNoName, endImpColNoName, srtImpColName, endImpColName;

            int srtCatNo, srtSubNo;
            int LineNO = 1;
            int rowNo = 5;
             
            string CatName = lstcmpt[0].cmpt_mainCat.ToString();
            oSheet.Cells[5, 1] = CatName;

            srtCatNo = 5;
            srtSubNo = 5;
            string SubName = lstcmpt[0].cmpt_name.ToString();
            oSheet.Cells[5, 2] = SubName;
            int x = 0;
            foreach (Components oRstImpacts in lstcmpt)
            {
                z = 5;

                if (CatName != oRstImpacts.cmpt_mainCat)
                {
                    srtCatNo = rowNo;
                    CatName = oRstImpacts.cmpt_mainCat;
                    oSheet.Cells[rowNo, 1] = CatName;
                }

                if (SubName != oRstImpacts.cmpt_name)
                {
                    srtSubNo = rowNo;
                    SubName = oRstImpacts.cmpt_name;
                    oSheet.Cells[rowNo, 2] = SubName;
                }

                startColNo = 69;
                startColName = GetExcelColumnName(startColNo).ToString() + rowNo;

                endColNo = startColNo + 1;
                endColName = GetExcelColumnName(endColNo).ToString() + rowNo;

                oSheet.Cells[rowNo, 4] = lstImpacts[x].impt_name;
                oSheet.get_Range(GetExcelColumnName(rowNo) + 4).Borders.Weight = 2;
                oSheet.get_Range(GetExcelColumnName(rowNo) + 4).Font.Bold = true;
                x++;
                oSheet.Cells[rowNo, 3] = LineNO;
                srtImpColNoName = "C" + rowNo;
                endImpColNoName = "C" + (rowNo + 1);

                srtImpColName = "D" + rowNo;
                endImpColName = "D" + (rowNo + 1);

                // --- MATRIX DATA ----
                foreach (Activities oRstAct in lstActivities)
                {
                    endColNo = startColNo + 1;
                    endColName = GetExcelColumnName(endColNo).ToString() + rowNo;

                    oSheet.get_Range(GetExcelColumnName(z) + rowNo).ColumnWidth = 2.5;
                    oSheet.get_Range(GetExcelColumnName(z)+rowNo).Borders.Weight = 2;
                   //  DrawCellBorders(rowNo, z  , rowNo, z  , true, false, false, true, true);
                   //  DrawCellBorders(rowNo  , z, rowNo  , z, false, true, true, false, true);
                    //  DrawCellBorders(rowNo  , z  , rowNo  , z , false, true, false, true, false);
                   //   DrawCellBorders(rowNo+1, z+1, rowNo+1, z+1, false, true, false, true, false);
                      //DrawCellBorders(rowNo, z, rowNo, z, true, true, false, true, true);
                    startColNo = endColNo + 1;
                    startColName = GetExcelColumnName(startColNo).ToString() + rowNo;

                    z = z + 1;
                }


                //  oSheet.Cells[rowNo, z + 1] = (float)Total / MaxTotal;
                //  oSheet.Cells[rowNo, z + 2] = 42;
                endColName = GetExcelColumnName(startColNo) + (rowNo + 1).ToString();
                //oSheet.get_Range(startColName, endColName).Merge();

                rowNo = rowNo + 2;
                LineNO = LineNO + 1;
            }

            //   oSheet.Cells[4, z + 2] = "Physical Impact";
            ///   oSheet.Cells[4, z + 3] = "Sociological Impact";
            //   oSheet.Cells[4, z + 4] = "Echological Impact";
           // oXL.Cells.Locked = false;
           // oXL.get_Range("A1", "N4").Locked = true;
           // oXL.get_Range("A4", "D50").Locked = true;
        //    oSheet.Protect(Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing,
    //Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);


            oXL.Visible = true;
            oXL.UserControl = true;
            filePath = @"D:\cecbTemp\" + prj.proj_name + "-" + prj.proj_reference + " - " + String.Format("{0: dd MMM yyyy}", DateTime.Now); ;

            bool folderExists = Directory.Exists(filePath);
            if (!folderExists)
            {
                Directory.CreateDirectory(filePath);

            }

            oWB.SaveCopyAs(filePath + @"\" + mem.mem_name + "-" + com + "-Simple.xlsx");
            MessageBox.Show("Excel File saved succsessfully", "Message", MessageBoxButton.OK, MessageBoxImage.Information);


        }
        //================================================================================================

        private string GetExcelColumnName(int columnNumber)
        {
            int dividend = columnNumber;
            string columnName = String.Empty;
            int modulo;

            while (dividend > 0)
            {
                modulo = (dividend - 1) % 26;
                columnName = Convert.ToChar(65 + modulo).ToString() + columnName;
                dividend = (int)((dividend - modulo) / 26);
            }

            return columnName;
        }

        private void DrawCellBorders(int frmrowNo, int frmColNo, int TorowNo, int ToColNo, bool top, bool bottom, bool left, bool right, bool diagup)
        {
            Excel.Borders b = null;
            Excel.Borders fb = null;

            b = (Excel.Borders)(oSheet.Cells[frmrowNo, frmColNo] as Excel.Range).Borders;
            fb = (oSheet.Cells[frmrowNo, frmColNo] as Excel.Range).Borders;


            if (diagup == true)
            {
                fb[Microsoft.Office.Interop.Excel.XlBordersIndex.xlDiagonalUp].Weight = b[Microsoft.Office.Interop.Excel.XlBordersIndex.xlDiagonalUp].Weight;
            }
            if (top == true)
            {
                fb[Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeTop].Weight = b[Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeTop].Weight;
            }
            if (bottom == true)
            {
                fb[Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeBottom].Weight = b[Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeBottom].Weight;
            }
            if (left == true)
            {
                fb[Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeLeft].Weight = b[Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeLeft].Weight;
            }

            if (right == true)
            {
                fb[Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeRight].Weight = b[Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeRight].Weight;
            }

        }

        #endregion

        private void button3_Click(object sender, RoutedEventArgs e)
        {

            try
            {

                Process.Start(filePath);
            }
            catch (Exception)
            {
                MessageBox.Show("Select a Project and member", "Message", MessageBoxButton.OK, MessageBoxImage.Information);

            }
        }

        private void buttonSendAllEmail_Click(object sender, RoutedEventArgs e)
        {
            buttonSendAllEmail.IsEnabled = false;
            this.Cursor = Cursors.Wait;
            String from = new cecbClasses.Data_Adapters.Setting.Func_Settings().getLastMail();

            String cc = "";
            String displayName = "CECB";
            String password = new cecbClasses.Data_Adapters.Setting.Func_Settings().getLastMailPass();
            String Subjet = prj.proj_reference.ToString();
            String Body = "   ";

            List<Members> memList = (List<Members>)listBoxMembers.ItemsSource;

            try
            {
                int mcount = 0;
                foreach (Members _mem in memList)
                {
                    try
                    {
                        //buttonSendAllEmail.Content = "Sending " +mcount+1;
                        String memMail = _mem.MemberEmails.First().mem_email.ToString();
                        bool mailSent = new cecbClasses.Data_Adapters.Email.ActivationCodeEmail().genarateEmailToAll(from, memMail, cc, displayName, password, Subjet, Body, filePath, _mem.mem_name);
                        if(mailSent)
                            mcount++;

                    }
                    catch (Exception)
                    {
                        MessageBox.Show(_mem.mem_name + "'s Email Not Found", "Message", MessageBoxButton.OK, MessageBoxImage.Error);

                        break;
                    }




                }
                MessageBox.Show(mcount + "  Emails sent Succesfully", "Message", MessageBoxButton.OK, MessageBoxImage.Information);
                new cecbClasses.Data_Adapters.Email.ActivationCodeEmail().updateProjectIssentEmail(prj);
            }
            catch (Exception)
            {

                throw;
            }



            this.Cursor = Cursors.Arrow;
            buttonSendAllEmail.IsEnabled = true;
            buttonSendAllEmail.Content = "Send Email ";
        }

        private void listBoxComponents_SelectionChanged(object sender, SelectionChangedEventArgs e)
        {

        }

        private void ButtonClose_Click(object sender, RoutedEventArgs e)
        {
            this.Close();
        }



    }
}
